import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import com.jadcon.bedbreakfast.domain.Suite;
import com.jadcon.bedbreakfast.util.DbUtils;


public class TestRepository {

	private SimpleJdbcTemplate simpleJdbcTemplate = DbUtils.getSimpleJdbcTemplate();

	public TestRepository() {
		
		// dataSource is a common abstraction and creates a connection pool, used in production
		// driver manager is a simple single connection only used for testing
		//DriverManagerDataSource dataSource = new DriverManagerDataSource();
		//dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		//dataSource.setUrl("jdbc:mysql://localhost:3306/bedandbreakfast");
		//dataSource.setUsername("root");
		//dataSource.setPassword("");
		//this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
		// connection pools can be created for performance
		// connection pool is wrapped by dataSource and is injected into JbbcTemplae
		
	}
	
	public List<Suite> getSuiteListJdbc() {

		List<Suite> suiteList = new ArrayList<Suite>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			String sql = "SELECT S_id, name, number, beds, cost, description, imageName FROM suite";
			conn = DbUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				String name = rs.getString("name");
				int number = rs.getInt("number");
				int beds = rs.getInt("beds");
				int cost = rs.getInt("cost");
				String description = rs.getString("description");
				String imageName = rs.getString("imageName");

				Suite suite = new Suite(name, number, beds, description,
						imageName, cost);

				suiteList.add(suite);

			}
			return suiteList;
			
		} catch (SQLException e) {
			DbUtils.convertCheckedExceptionToRuntime(e);
		} finally {
			DbUtils.close(conn, pstmt, rs);
		}
		return null;
	}
	
	public List<Suite> getSuiteListSimpleJdbcTemplate() {

		String sql = "SELECT S_id, name, number, beds, cost, description, imageName FROM suite";

	    ParameterizedRowMapper<Suite> mapper = new ParameterizedRowMapper<Suite>() {
	        public Suite mapRow(ResultSet rs, int rowNum) throws SQLException {
				String name = rs.getString("name");
				int number = rs.getInt("number");
				int beds = rs.getInt("beds");
				int cost = rs.getInt("cost");
				String description = rs.getString("description");
				String imageName = rs.getString("imageName");

				Suite suite = new Suite(name, number, beds, description,
						imageName, cost);
				return suite;
	        }
	    };

	    return this.simpleJdbcTemplate.query(sql, mapper);

	}
}
